package fr.greta.centre.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import fr.greta.centre.metier.Promotion;

public class PromotionDAOImpl implements PromotionDAO {

	private Statement st;

	public int save(Promotion prom) throws SQLException {
		try {
			Connection conn = Bd.getConnect();
			st = conn.createStatement();
			String sql = "INSERT INTO `centre_formation`.`promotion` (id_cursus, date_debut, date_fin, date_debut_inscription, date_fin_inscription)"
					+ "VALUES"
					+ prom.getIdCursus()
					+ prom.getDebut()
					+ prom.getFin()
					+ prom.getDebutInscription()
					+ prom.getFin();
			st.executeUpdate(sql);
			st.getConnection().close();

		} catch (SQLException e) {
			throw e;
		}
		return 0;
	}

	public boolean delete(int idPromotion) {

		return false;
	}

	public Promotion findByKey(int idPromotion) throws SQLException {
		Promotion result = null;
		try {
			Connection connexion = Bd.getConnect();
			st = connexion.createStatement();
			String sql = "SELECT id_promotion, cursus.id_cursus, date_debut, date_fin, libelle, date_debut_inscription, date_fin_inscription "
					+ "FROM promotion INNER JOIN cursus ON promotion.id_cursus = cursus.id_cursus "
					+ "WHERE id_promotion =" + idPromotion;
			ResultSet rs = st.executeQuery(sql);
			if (rs.next()) {

				result = new Promotion(rs.getInt("id_cursus"),
						rs.getInt("id_promotion"), rs.getDate("date_debut"),
						rs.getDate("date_fin"),
						rs.getDate("date_debut_inscription"),
						rs.getDate("date_fin_inscription"));

			}
			st.getConnection().close();
		} catch (SQLException e) {

			throw e;

		}
		return result;
	}

	public ArrayList<Promotion> findAll() throws SQLException {

		ArrayList<Promotion> lesPromotions = new ArrayList<Promotion>();

		try {
			Connection connect = Bd.getConnect();
			String sql = "SELECT * FROM promotion";
			ResultSet resultat;
			resultat = connect.createStatement().executeQuery(sql);
			while (resultat.next()) {

				Promotion unePromotion = new Promotion(
						resultat.getInt("id_cursus"),
						resultat.getInt("id_promotion"),
						resultat.getDate("date_debut"),
						resultat.getDate("date_fin"),
						resultat.getDate("date_debut_inscription"),
						resultat.getDate("date_fin_inscription"));
				lesPromotions.add(unePromotion);
			}
			connect.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lesPromotions;
	}

public List<Promotion> getOuvertes() throws SQLException {

		
		ResultSet resultat;
		ArrayList<Promotion> lesPromotions = new ArrayList<Promotion>();

		try {
			Connection connect = Bd.getConnect();
			String SQL = "SELECT * FROM promotion WHERE now() between date_debut_inscription and date_fin_inscription";
			resultat = connect.createStatement().executeQuery(SQL);
			while (resultat.next()) {
				Promotion unePromotion = new Promotion(resultat.getInt("id_cursus"),
						resultat.getInt("id_promotion"),
						resultat.getDate("date_debut"),
						resultat.getDate("date_fin"),
						resultat.getDate("date_debut_inscription"),
						resultat.getDate("date_fin_inscription"));
				lesPromotions.add(unePromotion);
			}
			connect.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return lesPromotions;

	}

	public ArrayList<Promotion> findByIdCursus(int idCursus) {
		ArrayList<Promotion> lesPromotions = new ArrayList<Promotion>();
		String sql = "SELECT * FROM promotion WHERE id_cursus = ?";
		PreparedStatement st;
		try {
			st = Bd.getConnect().prepareStatement(sql);
			st.setInt(1, idCursus);
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				Promotion unePromotion = new Promotion(rs.getInt("id_cursus"),
						rs.getInt("id_promotion"), rs.getDate("date_debut"),
						rs.getDate("date_fin"),
						rs.getDate("date_debut_inscription"),
						rs.getDate("date_fin_inscription"));
				lesPromotions.add(unePromotion);
			}
			st.getConnection().close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return lesPromotions;

	}
}
